﻿using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace NFine.Data.Extensions
{
   public  class PHPDBDo
    {
    //        INSERT INTO  car
            //(car_id, car_no)
            // VALUES
                //('1','1')


        //新增
        /// <summary>
        /// 新增,使用数组放入,因而拆分数组
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static  string add( string tableName,Dictionary<object, object> obj,bool fetchsql=false)
        {
            string sql = "";
            sql += "INSERT INTO " + tableName.ToString();
            sql += "(";
            foreach (var item in obj)
            {
                sql += item.Key+",";
            }
            sql = sql.Remove(sql.Length-1,1);
            sql += ") values (";
            foreach (var item in obj)
            {
                sql +="'"+ item.Value + "',";
            }
            sql = sql.Remove(sql.Length - 1, 1);
            sql += ")";

            return sql;
        }

        
        /// <summary>
        /// 修改 使用数组放入,因而拆分数组
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string update(string tableName, Dictionary<object, object> obj, Dictionary<object, object> where, bool fetchsql = false)
        {
            string sql = "";
            sql += "update " + tableName.ToString()+" set ";
            foreach (var item in obj)
            {
                sql += item.Key+"='"+ item.Value + "',";
            }
            sql = sql.Remove(sql.Length - 1, 1);
            sql += " where ";
            foreach (var item in where)
            {
                sql += item.Key + "='" + item.Value + "' and ";
            }
            sql = sql.Remove(sql.Length - 4);

            return sql;
        }

        /// <summary>
        /// 删除 使用数组放入,因而拆分数组
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string delete(string tableName, Dictionary<object, object> where, bool fetchsql = false)
        {
            string sql = "";
            sql += "delete from " + tableName.ToString();
            sql += " where ";
            foreach (var item in where)
            {
                sql += item.Key + "='" + item.Value + "' and ";
            }
            sql = sql.Remove(sql.Length - 4);

            return sql;
        }

        public string DataTableToJsonWithJsonNet(DataTable table)
        {
            string jsonString = string.Empty;
            jsonString = JsonConvert.SerializeObject(table);
            return jsonString;
        }

        public static int Count(DataTable table)
        {
            return table.Rows.Count;
        }

        /// <summary>
        /// 会被注入攻击
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool Docommit(string sql)
        {
            using (TransactionScope transaction = new TransactionScope())//使用事务
            {
                try
                {
                    using (MySqlConnection conn = new MySqlConnection(DbHelper.connstring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = conn;
                            cmd.Parameters.AddWithValue("@111","aa");
                            conn.Open();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    transaction.Complete();//就这句就可以了。
                    return true;
                }
                catch (Exception ex) {
                    throw ex.InnerException;
                    
                }
            }
        }


        /// <summary>
        /// 会被注入攻击
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ListDocommit(string sql)
        {
            using (TransactionScope transaction = new TransactionScope())//使用事务
            {
                try
                {
                    using (MySqlConnection conn = new MySqlConnection(DbHelper.connstring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = conn;
                            cmd.Parameters.AddWithValue("@111", "aa");
                            conn.Open();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    transaction.Complete();//就这句就可以了。
                    return true;
                }
                catch (Exception ex)
                {
                    throw ex.InnerException;

                }
            }
        }

    }
}
